package com.roadjava.javaweb.student.dao.impl;

import com.roadjava.javaweb.student.beans.entity.StudentDO;
import com.roadjava.javaweb.student.beans.req.StudentRequest;
import com.roadjava.javaweb.student.beans.res.TableResult;
import com.roadjava.javaweb.student.beans.vo.StudentVO;
import com.roadjava.javaweb.student.dao.StudentDao;
import com.roadjava.javaweb.student.util.DBUtil;
import com.roadjava.javaweb.student.util.StringUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class StudentDaoImpl implements StudentDao {
    @Override
    public int addStudent(StudentDO studentDO) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return 0;
        }
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" insert into student(name,no,birth_day,maj) ");
        sb.append(" values(?,?,?,?) ");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,studentDO.getName());
            ps.setObject(2,studentDO.getNo());
            ps.setObject(3,studentDO.getBirthDay());
            ps.setObject(4,studentDO.getMaj());
            // 打印最终执行的sql语句
            System.out.println("addStudent执行的sql:" + ps.toString());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return 0;
    }

    @Override
    public TableResult<StudentVO> queryStudentByPage(StudentRequest studentRequest) {
        TableResult<StudentVO> tableResult = new TableResult<>();
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return null;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        // 查询学生列表
        StringBuilder sb = new StringBuilder();
        sb.append(" select * from student ");
        appendWhere(studentRequest,sb);
        sb.append(" order by id desc limit ").append(studentRequest.getStart())
                .append(",").append(studentRequest.getPageSize());
        try {
            ps = conn.prepareStatement(sb.toString());
            if (StringUtil.isNotBlank(studentRequest.getStudentName())) {
                ps.setObject(1,studentRequest.getStudentName());
            }
            // 打印最终执行的sql语句
            System.out.println("queryStudentByPage查询学生的sql:" + ps.toString());
            rs = ps.executeQuery();
            List<StudentVO> list = new ArrayList<>();
            while (rs.next()) {
                StudentVO studentVO = buildStudent(rs);
                list.add(studentVO);
            }
            tableResult.setData(list);// 设置数据
            /*
            查询总条数
             */
            sb.setLength(0);
            sb.append(" select count(*) from student ");
            appendWhere(studentRequest,sb);
            ps = conn.prepareStatement(sb.toString());
            if (StringUtil.isNotBlank(studentRequest.getStudentName())) {
                ps.setObject(1,studentRequest.getStudentName());
            }
            // 打印最终执行的sql语句
            System.out.println("queryStudentByPage查询学生总条数的sql:" + ps.toString());
            rs = ps.executeQuery();
            if (rs.next()) {
                int totalCount = rs.getInt(1);
                tableResult.setTotalCount(totalCount); // 设置总条数
            }
            return tableResult;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public StudentVO getStudentById(long studentId) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return null;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" select * from student where id = ? ");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,studentId);
            // 打印最终执行的sql语句
            System.out.println("getStudentById执行的sql:" + ps.toString());
            rs = ps.executeQuery();
            if (rs.next()) {
                return buildStudent(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    @Override
    public int updateStudent(StudentDO studentDO) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return 0;
        }
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" update student set name=?,no=?,birth_day=?,maj=? ");
        sb.append(" where id = ? ");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,studentDO.getName());
            ps.setObject(2,studentDO.getNo());
            ps.setObject(3,studentDO.getBirthDay());
            ps.setObject(4,studentDO.getMaj());
            ps.setObject(5,studentDO.getId());
            // 打印最终执行的sql语句
            System.out.println("updateStudent执行的sql:" + ps.toString());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return 0;
    }

    @Override
    public int deleteStudentById(long studentId) {
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return 0;
        }
        PreparedStatement ps = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" delete from  student  where id = ? ");
        try {
            ps = conn.prepareStatement(sb.toString());
            ps.setObject(1,studentId);
            // 打印最终执行的sql语句
            System.out.println("deleteStudentById执行的sql:" + ps.toString());
            return ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return 0;
    }

    @Override
    public List<StudentVO> loadAllStudents() {
        List<StudentVO> list = new ArrayList<>();
        Connection conn = DBUtil.getConn();
        if (conn == null) {
            return null;
        }
        PreparedStatement ps = null;
        ResultSet rs = null;
        StringBuilder sb = new StringBuilder();
        sb.append(" select * from student order by id desc ");
        try {
            ps = conn.prepareStatement(sb.toString());
            // 打印最终执行的sql语句
            System.out.println("loadAllStudents执行的sql:" + ps.toString());
            rs = ps.executeQuery();
            while (rs.next()) {
                list.add(buildStudent(rs));
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeResultSet(rs);
            DBUtil.closePs(ps);
            DBUtil.closeConn(conn);
        }
        return null;
    }

    private StudentVO buildStudent(ResultSet rs) throws SQLException {
        long id = rs.getLong("id");
        String name = rs.getString("name");
        String no = rs.getString("no");
        String birth_day = rs.getString("birth_day");
        String maj = rs.getString("maj");
        StudentVO studentVO = new StudentVO();
        studentVO.setId(id);
        studentVO.setName(name);
        studentVO.setNo(no);
        studentVO.setBirthDay(birth_day);
        studentVO.setMaj(maj);
        return studentVO;
    }

    private void appendWhere(StudentRequest studentRequest, StringBuilder sb) {
        String studentName = studentRequest.getStudentName();
        if (StringUtil.isNotBlank(studentName)) {
            sb.append(" where name = ? ");
        }
    }
}
